package cn.cttic.dashboard.mapper;

import cn.cttic.dashboard.domain.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 工作台
 *
 * @author Times.Studio
 * @date 2024-09-30
 */
public interface WorkbenchMapper {

    @Select("<script>" +
        "SELECT\n" +
        "	SUM(IF(DATE(bso.create_time) = DATE(NOW()), 1, 0)) as todaySaleCount,\n" +
        "	SUM(IF(bso.`status` = 0 or bso.`status` = 9, 1, 0)) as totalWaitingCancelCount,\n" +
        "	SUM(IF(DATE(bio.start_time) = DATE(NOW()), 1, 0)) as todayInstallCount,\n" +
        "	SUM(IF(bio.`status` = 6, 1, 0)) as totalProblemCount,\n" +
        "	SUM(IF(DATE(bio.active_time) = DATE(NOW()), 1, 0)) as todayActiveCount,\n" +
        "	SUM(IF(bio.`status` = 2 or bio.active_status &lt;&gt; 1, 1, 0)) as totalWaitingActiveCount,\n" +
        "	SUM(IF(DATE(bio.verify_time) = DATE(NOW()), 1, 0)) as todayVerifyCount,\n" +
        "	SUM(IF(bio.`status` = 2 or bio.verify_status != 1, 1, 0)) as totalWaitingVerifyCount,\n" +
        "	SUM(IF(DATE(bio.review_time) = DATE(NOW()), 1, 0)) as todayQualityControlCount,\n" +
        "	SUM(IF(bio.review_time is not null, 1, 0)) as totalQualityControlCount,\n" +
        "	SUM(IF(bio.review_time is not null and bio.review_status = 1, 1, 0)) as totalQualityControlPassCount\n" +
        "FROM\n" +
        "	base_sale_order bso\n" +
        "	LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
        "	INNER JOIN base_product bp ON bp.id = bso.product_id" +
        "</script>")
    FullBenchmarkVo getFullBenchmark();

    @Select("<script>" +
        "SELECT\n" +
        "	RANK() OVER ( ORDER BY t.totalSaleCount DESC, t.activeRatePercentage DESC ) AS rankIndex,\n" +
        "	t.* \n" +
        "FROM\n" +
        "	(SELECT\n" +
        "		bc.company_name AS companyName,\n" +
        "		COUNT( bso.id ) AS totalSaleCount,\n" +
        "		SUM(IF( bio.active_status = 1, 1, 0 )) AS totalActiveCount,\n" +
        "		SUM(IF( bio.active_status = 1, 1, 0 )) / COUNT( bso.id ) AS activeRatePercentage \n" +
        "	FROM\n" +
        "		base_sale_order bso\n" +
        "		LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
        "		INNER JOIN base_user bu ON bu.id = bso.sales_man_id\n" +
        "		INNER JOIN base_company bc ON bc.id = bu.company_id \n" +
        "	GROUP BY\n" +
        "		bc.id \n" +
        "	ORDER BY\n" +
        "		totalSaleCount DESC,\n" +
        "		activeRatePercentage DESC \n" +
        "		LIMIT 5 \n" +
        "	) t" +
        "</script>")
    List<SaleRankVo> getSaleRank();

    @Select("<script>" +
        "SELECT\n" +
        "	RANK() OVER ( ORDER BY t.totalInstallCount DESC, t.activeRatePercentage DESC, t.verifyPassRatePercentage DESC ) AS rankIndex,\n" +
        "	t.* \n" +
        "FROM\n" +
        "	(SELECT\n" +
        "		bc.company_name AS companyName,\n" +
        "		SUM(IF(bio.install_status = 1, 1, 0)) as totalInstallCount,\n" +
        "		SUM(IF(bio.active_status = 1, 1, 0)) as totalActiveCount,\n" +
        "		SUM(IF(bio.active_status = 1, 1, 0)) / SUM(IF(bio.install_status = 1, 1, 0)) AS activeRatePercentage,\n" +
        "		SUM(IF(bio.verify_status = 1, 1, 0)) / SUM(IF(bio.install_status = 1, 1, 0)) AS verifyPassRatePercentage \n" +
        "	  FROM\n" +
        "		base_sale_order bso\n" +
        "		LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
        "		INNER JOIN base_user bu ON bu.id = bso.sales_man_id\n" +
        "		INNER JOIN base_company bc ON bc.id = bu.company_id \n" +
        "	GROUP BY\n" +
        "		bc.id \n" +
        "	ORDER BY\n" +
        "		totalInstallCount DESC,\n" +
        "		activeRatePercentage DESC, \n" +
        "		verifyPassRatePercentage DESC \n" +
        "		LIMIT 5 \n" +
        "	) t" +
        "</script>")
    List<InstallRankVo> getInstallRank();

    @Select("<script>" +
        "SELECT RANK() OVER (ORDER BY t.totalVerifyCount DESC) as rankIndex, t.* FROM (\n" +
        "	SELECT\n" +
        "		rsd.dept_name AS companyName,\n" +
        "		tc.totalInstallCount,\n" +
        "		SUM(IF( bio.verify_status is not null, 1, 0 )) AS totalVerifyCount,\n" +
        "		SUM(IF( bio.verify_status is not null, 1, 0 )) / tc.totalInstallCount AS verifyPassRatePercentage \n" +
        "	FROM\n" +
        "		(SELECT COUNT(*) as totalInstallCount FROM base_install_order WHERE install_status = 1) tc, base_sale_order bso\n" +
        "		LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
        "		INNER JOIN sys_user su ON su.user_id = bio.received_user_id\n" +
        "		LEFT JOIN sys_dept sd ON su.dept_id = sd.dept_id \n" +
        "		LEFT JOIN sys_dept rsd ON rsd.dept_id = SUBSTRING_INDEX(SUBSTRING_INDEX(sd.ancestors,',', 3), ',', -1)\n" +
        "	GROUP BY\n" +
        "		rsd.dept_id \n" +
        "	ORDER BY\n" +
        "		totalVerifyCount DESC\n" +
        "		LIMIT 5 \n" +
        "	) t" +
        "</script>")
    List<VerifyRankVo> getVerifyRank();

//    @Select("<script>" +
//        "SELECT RANK() OVER (ORDER BY t.totalVerifyCount DESC, t.verifyPassRatePercentage DESC) as rankIndex, t.* FROM (\n" +
//        "	SELECT\n" +
//        "		bc.company_name AS companyName,\n" +
//        "		SUM(IF( bio.install_status = 1, 1, 0)) AS totalInstallCount,\n" +
//        "		SUM(IF( bio.verify_time is not null, 1, 0 )) AS totalVerifyCount,\n" +
//        "		SUM(IF( bio.verify_time is not null, 1, 0 )) / SUM(IF( bio.install_status = 1, 1, 0)) AS verifyPassRatePercentage \n" +
//        "	FROM\n" +
//        "		base_sale_order bso\n" +
//        "		LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
//        "		INNER JOIN base_user bu ON bu.id = bso.sales_man_id\n" +
//        "		INNER JOIN base_company bc ON bc.id = bu.company_id \n" +
//        "	GROUP BY\n" +
//        "		bc.id \n" +
//        "	ORDER BY\n" +
//        "		totalVerifyCount DESC,\n" +
//        "		verifyPassRatePercentage DESC \n" +
//        "		LIMIT 5 \n" +
//        "	) t" +
//        "</script>")
//    List<VerifyRankVo> getVerifyRank();


    @Select("<script>" +
        "SELECT RANK() OVER (ORDER BY t.totalSaleCount DESC, t.totalInstallCount DESC, t.totalActiveCount DESC, t.totalVerifyCount DESC) as rankIndex, t.* FROM (SELECT\n" +
        "	bp.product_name,\n" +
        "	COUNT(bso.id) as totalSaleCount,\n" +
        "	SUM(IF(bio.install_status = 1, 1, 0)) as totalInstallCount,\n" +
        "	SUM(IF(bio.active_status = 1, 1, 0)) as totalActiveCount,\n" +
        "	SUM(IF(bio.verify_status = 1, 1, 0)) as totalVerifyCount \n" +
        "FROM\n" +
        "	base_sale_order bso\n" +
        "	LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
        "	INNER JOIN base_product bp ON bp.id = bso.product_id\n" +
        "	GROUP BY bp.id\n" +
        "ORDER BY \n" +
        "	totalSaleCount DESC,\n" +
        "	totalInstallCount DESC,\n" +
        "	totalActiveCount DESC,\n" +
        "	totalVerifyCount DESC\n" +
        "LIMIT 5) t" +
        "</script>")
    List<ProductCounterStatisticVo> getProductCounterStatistics();

    @Select("<script>" +
        "SELECT\n" +
        "	bp.product_name as productName,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 1, 1, 0)) as m1 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 2, 1, 0)) as m2 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 3, 1, 0)) as m3 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 4, 1, 0)) as m4 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 5, 1, 0)) as m5 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 6, 1, 0)) as m6 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 7, 1, 0)) as m7 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 8, 1, 0)) as m8 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 9, 1, 0)) as m9 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 10, 1, 0)) as m10 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 11, 1, 0)) as m11 ,\n" +
        "  SUM(IF(MONTH(bso.create_time) = 12, 1, 0)) as m12 ,\n" +
        "	COUNT(bso.id) as totalCount\n" +
        "FROM\n" +
        "	base_sale_order bso\n" +
        "	INNER JOIN base_product bp ON bp.id = bso.product_id\n" +
        "	LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
        "WHERE\n" +
        "	YEAR(bso.create_time) = YEAR(NOW())\n" +
        "   <choose>" +
        "       <when test=\"productId != null and productId != &apos;&apos;\">\n" +
        "           AND bp.id = ${productId} \n" +
        "       </when>\n" +
        "       <otherwise>\n" +
        "	        AND bp.id IN (\n" +
        "	        	SELECT t.id FROM (SELECT \n" +
        "	        		bp.id, bp.product_name\n" +
        "	        	FROM \n" +
        "	        		base_sale_order bso\n" +
        "	        	INNER JOIN base_product bp ON bp.id = bso.product_id\n" +
        "	        	LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
        "	        	WHERE\n" +
        "	        		YEAR(bso.create_time) = YEAR(NOW())\n" +
        "	        	GROUP BY bp.id\n" +
        "	        	ORDER BY COUNT(bso.id) DESC\n" +
        "	        	LIMIT 5 ) t\n" +
        "	        )\n" +
        "       </otherwise>\n" +
        "   </choose>\n" +
        "GROUP BY \n" +
        "  bp.id\n" +
        "ORDER BY \n" +
        "	totalCount DESC" +
        "</script>")
    List<ProductSaleByMonthOverYearTableVo> getProductSaleByMonthOverYear(
        @Param(value = "productId") String productId
    );

//    @Select("<script>" +
//        "SELECT\n" +
//        "	bp.product_name,\n" +
//        "	YEAR(bso.create_time) as yearVal,\n" +
//        "	MONTH(bso.create_time) as monthVal,\n" +
//        "	COUNT(bso.id) as totalSaleCount,\n" +
//        "	SUM(IF(bio.install_status = 1, 1, 0)) as totalInstallCount,\n" +
//        "	SUM(IF(bio.active_status = 1, 1, 0)) as totalActiveCount,\n" +
//        "	SUM(IF(bio.verify_status = 1, 1, 0)) as totalVerifyCount \n" +
//        "FROM\n" +
//        "	base_sale_order bso\n" +
//        "	INNER JOIN base_product bp ON bp.id = bso.product_id\n" +
//        "	LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
//        "WHERE\n" +
//        "	YEAR(bso.create_time) = YEAR(NOW())\n" +
//        "   <choose>" +
//        "       <when test=\"productId != null and productId != &apos;&apos;\">\n" +
//        "           AND bp.id = ${productId} \n" +
//        "       </when>\n" +
//        "       <otherwise>\n" +
//        "	        AND bp.id IN (\n" +
//        "	        	SELECT t.id FROM (SELECT \n" +
//        "	        		bp.id, bp.product_name\n" +
//        "	        	FROM \n" +
//        "	        		base_sale_order bso\n" +
//        "	        	INNER JOIN base_product bp ON bp.id = bso.product_id\n" +
//        "	        	LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
//        "	        	WHERE\n" +
//        "	        		YEAR(bso.create_time) = YEAR(NOW())\n" +
//        "	        	GROUP BY bp.id\n" +
//        "	        	ORDER BY COUNT(bso.id) DESC\n" +
//        "	        	LIMIT 5 ) t\n" +
//        "	        )\n" +
//        "       </otherwise>\n" +
//        "   </choose>\n" +
//        "GROUP BY \n" +
//        "  YEAR(bso.create_time), MONTH(bso.create_time), bp.id\n" +
//        "ORDER BY \n" +
//        "	totalSaleCount DESC,\n" +
//        "	totalInstallCount DESC,\n" +
//        "	totalActiveCount DESC,\n" +
//        "	totalVerifyCount DESC" +
//        "</script>")
//    List<ProductSaleByMonthOverYearVo> getProductSaleByMonthOverYear(
//        @Param(value = "productId") String productId
//    );

    @Select("<script>" +
        "SELECT\n" +
        "	DATE(bso.create_time) as dateVal,\n" +
        "	COUNT(bso.id) as totalSaleCount,\n" +
        "	SUM(IF(bio.install_status = 1, 1, 0)) as totalInstallCount,\n" +
        "	SUM(IF(bio.install_status = 1, 1, 0)) / COUNT(bso.id) as installRatePercentage\n" +
        "FROM\n" +
        "	base_sale_order bso\n" +
        "	INNER JOIN base_product bp ON bp.id = bso.product_id\n" +
        "	LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
        "WHERE\n" +
        "	bso.create_time &gt;= #{start}\n" +
        "	AND bso.create_time &lt;= #{end}\n" +
        "GROUP BY \n" +
        "  DATE(bso.create_time)\n" +
        "ORDER BY \n" +
        "	DATE(bso.create_time) ASC" +
        "</script>")
    List<SaleInstallByDayOverPeriodVo> getSaleInstallByDayOverPeriod(
        @Param(value = "start") LocalDateTime start,
        @Param(value = "end") LocalDateTime end);

    @Select("<script>" +
        "SELECT\n" +
        "	YEAR(bso.create_time) as yearVal,\n" +
        "	MONTH(bso.create_time) as monthVal,\n" +
        "	COUNT(bso.id) as totalSaleCount,\n" +
        "	SUM(IF(bio.install_status = 1, 1, 0)) as totalInstallCount,\n" +
        "	SUM(IF(bio.active_status = 1, 1, 0)) as totalActiveCount,\n" +
        "	SUM(IF(bio.verify_status = 1, 1, 0)) as totalVerifyCount ,\n" +
        "	SUM(IF(bio.review_status = 1, 1, 0)) as totalReviewCount \n" +
        "FROM\n" +
        "	base_sale_order bso\n" +
        "	INNER JOIN base_product bp ON bp.id = bso.product_id\n" +
        "	LEFT JOIN base_install_order bio ON bio.sale_order_id = bso.id\n" +
        "WHERE\n" +
        "	YEAR(bso.create_time) = ${iYear}\n" +
        "GROUP BY \n" +
        "  YEAR(bso.create_time), MONTH(bso.create_time)" +
        "</script>")
    List<WorkStatisticByMonthOverYearVo> getWorkStatisticByMonthOverYear(
        @Param(value = "iYear") Integer year
    );
}
